﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using System.Data.OleDb;
using System.Data.Common;
using QLDT.QLDT_GVTableAdapters;
using DTO;
using BUS;

namespace QLDT
{
    public partial class NhapxuatExcelGiangVien : DevExpress.XtraEditors.XtraForm
    {
        public NhapxuatExcelGiangVien()
        {
            InitializeComponent();
        }

        private void simpleBt_browse_Click(object sender, EventArgs e)
        {
            //openFileDialogExel.Filter = "Excel Files |*.xls|Excel Files 2007 |*.xlsx";
            openFileDialog_GV.FileName = "";
            openFileDialog_GV.ShowDialog();
        }

        private void cmb_sheeet_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                String connec = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;""", txt_path.Text);
                String query = String.Format("Select * from [{0}]", cmb_sheeet.Text);
                OleDbDataAdapter adapter = new OleDbDataAdapter(query, connec);
                System.Data.DataTable tbGiangVien = new System.Data.DataTable();
                adapter.Fill(tbGiangVien);
                if (tbGiangVien.Rows.Count > 0)
                {
                    dataGridViewGV.DataSource = tbGiangVien;
                }
                else
                {
                    dataGridViewGV.DataSource = null;
                }
            }
            catch (Exception)
            {
                //MessageBox.Show("Chưa chọn File hoặc đường dẫn File sai");
            }
        }

        private void openFileDialog_GV_FileOk(object sender, CancelEventArgs e)
        {
            String urlFile = openFileDialog_GV.FileName;
            txt_path.Text = urlFile;
            List<String> sheets = getListSheet(urlFile);
            cmb_sheeet.DataSource = sheets;
        }
        private List<String> getListSheet(String urlFile)
        {
            try
            {
                List<String> sheets = new List<String>();
                String connec = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;""", urlFile);
                DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
                DbConnection connection = factory.CreateConnection();
                connection.ConnectionString = connec;
                connection.Open();
                System.Data.DataTable dt = connection.GetSchema("Tables");
                connection.Close();
                foreach (DataRow row in dt.Rows)
                {
                    String sheetnames = (String)row["TABLE_NAME"];
                    sheets.Add(sheetnames);
                }
                return sheets;
            }
            catch (Exception)
            {
                return null;
            }
        }

        private void simpleBt_thucthi_Click(object sender, EventArgs e)
        {
            GIANGVIENTableAdapter adapter = new GIANGVIENTableAdapter();
            int count = 0;

            foreach (DataGridViewRow row in dataGridViewGV.Rows)
            {
                try
                {
                    DTO_GiangVien GV = new DTO_GiangVien();
                    GV.MaGV = row.Cells["MaGV"].Value.ToString();
                    GV.TenGV = row.Cells["TenGV"].Value.ToString();
                    GV.DienThoai = row.Cells["DienThoai"].Value.ToString();
                    if (row.Cells["GioiTinh"].Value.ToString().Equals("1"))
                    {
                        GV.GioiTinh = true;
                    }
                    else
                    {
                        GV.GioiTinh = false;
                    }
                    GV.Email = row.Cells["Email"].Value.ToString();

                    GV.MaCN = row.Cells["MaCN"].Value.ToString();
                    GV.CNBoMon = row.Cells["CNBoMon"].Value.ToString();
                    GV.LVCN = row.Cells["LVCN"].Value.ToString();

                    GV.MaKhoa = row.Cells["MaKhoa"].Value.ToString();
                    //adapter.InsertQuery(MaSV, TenSV, GioiTinh, NgaySinh, MaLop, DiaChi, Email, SDT);
                    BUS_GiangVien.insertGiangVien(GV);
                    count++;
                }
                catch
                {
                    continue;
                }
            }
            MessageBox.Show("Đã thêm thành công" + count + "Và thất bại" + (dataGridViewGV.RowCount - count) + "");
        }
    }
}